import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;


public class MemInfoDAO implements MemInfoService{
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	private static final String INSERTSQL="insert into users(userid,uname, sex, uaddr, phone) values(userid.nextval,?,?,?,?)";
	private static final String DELETESQL="delete from users where userid = ?";
	private static final String UPDATESQL="update users set uname=?,phone=? where userid = ?";
	private static final String SELECTSQL="select * from users order by userid desc";
	
	private int i;
	@Override
	public int insertMem(MemInfoVO mv) {
		Connection conn = null;
		PreparedStatement ps = null;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(INSERTSQL);
				ps.setString(1, mv.getUname());
				ps.setString(2, mv.getSex());
				ps.setString(3, mv.getUaddr());
				ps.setString(4, mv.getPhone());
			i = ps.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(ps!=null) try{ps.close();} catch(Exception e){ e.printStackTrace();}
			if(conn!=null) try{conn.close();} catch(Exception e){ e.printStackTrace();}
		}
		return i;
	}

	@Override
	public int deleteMem(String uid) {
		Connection conn = null;
		PreparedStatement ps = null;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(DELETESQL);
				ps.setString(1, uid);
			i = ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(ps!=null) try{ps.close();} catch(Exception e){ e.printStackTrace();}
			if(conn!=null) try{conn.close();} catch(Exception e){ e.printStackTrace();}
		}
		return i;
	}

	@Override
	public int updateMem(MemInfoVO mv) {
		Connection conn = null;
		PreparedStatement ps = null;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(UPDATESQL);
				ps.setString(1, mv.getUname());
				ps.setString(2, mv.getPhone());
				ps.setString(3, mv.getUid());
			i = ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(ps!=null) try{ps.close();} catch(Exception e){ e.printStackTrace();}
			if(conn!=null) try{conn.close();} catch(Exception e){ e.printStackTrace();}
		}
		return i;
	}

	@Override
	public ArrayList<MemInfoVO> selectMem() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<MemInfoVO> data = new ArrayList<MemInfoVO>();
		MemInfoVO mv = null;
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(SELECTSQL);
			rs = ps.executeQuery();
			while(rs.next()){
				mv = new MemInfoVO(rs.getString(2), rs.getString(4), rs.getString(5), rs.getString(6));
				mv.setUid(rs.getString(1));
				mv.setuRegDate(rs.getString(3));
				data.add(mv);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();} catch(Exception e){ e.printStackTrace();}
			if(ps!=null) try{ps.close();} catch(Exception e){ e.printStackTrace();}
			if(conn!=null) try{conn.close();} catch(Exception e){ e.printStackTrace();}
		}
		return data;
	}

}
